/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Rating;

/**
 *
 * @author Student
 */
public class DaoRate {

    private String songId;

    public boolean addRating(Rating rating) throws SQLException {
        boolean success = false;

        Connector connect = new Connector();
        Connection conn = connect.getConn();

        PreparedStatement ps = conn.prepareStatement("INSERT INTO ROOT.RATING_LOG ("
                + " USER_ID,"
                + " SONG_ID, "
                + " RATING, "
                + " REVIEW) "
                + " VALUES "
                + " (?, ?, ?, ?)");


        ps.setString(1, rating.getUseriD());
        ps.setString(2, rating.getSongId());
        ps.setString(3, rating.getRating());
        ps.setString(4, rating.getReview());

        success = ps.executeUpdate() != 0 ? true : false;

        ps = conn.prepareStatement(" update SONG "
                + "     SET RATING = "
                + "             (SELECT AVG(rating) FROM RATING_LOG "
                + "             where SONG_ID = ?) "
                + "     where SONG_ID = ?");
        ps.setString(1, rating.getSongId());
        ps.setString(2, rating.getSongId());
        ps.executeUpdate();

        success = ps.executeUpdate() != 0 ? true : false;

        ps = conn.prepareStatement(" update transaction_log "
                + "     SET reviewed = true "
                + "     where SONG_ID = ?");
        ps.setString(1, rating.getSongId());

        ps.executeUpdate();

        success = ps.executeUpdate() != 0 ? true : false;


        return success;
    }

    public ArrayList<Rating> getSongReview() throws SQLException {
        ArrayList<Rating> list = new ArrayList<Rating>();

        Rating rating = null;

        /*
         * select au.USERNAME, rl.SONG_ID, rl.RATING_ID, rl.RATING, rl.REVIEW,
         * au.USER_ID from ROOT.RATING_LOG rl inner join ACCOUNT_USER au on
         * rl.USER_ID=au.USER_ID where rl.song_id = 9
         */
        Connector connect = new Connector();
        Connection conn = connect.getConn();

        String query = " select "
                + " au.USERNAME, "
                + " rl.SONG_ID, "
                + " rl.RATING_ID, "
                + " rl.RATING, "
                + " rl.REVIEW, "
                + " au.USER_ID "
                + " from ROOT.RATING_LOG rl "
                + " inner join ACCOUNT_USER au on rl.USER_ID=au.USER_ID "
                + " where rl.song_id = ?";
        //    PreparedStatement ps = conn.prepareStatement(



        PreparedStatement ps = conn.prepareStatement(query);

        ps.setString(1, this.songId);

        ResultSet rs = ps.executeQuery();
//if was able to get a row
        while (rs.next()) {

            String newline = rs.getString("review");
            newline = newline.replaceAll("<br />", "\n");
            newline = newline.replaceAll("''", "'");

            rating = new Rating(
                    rs.getString("rating_id"),
                    rs.getString("user_id"),
                    rs.getString("username"),
                    rs.getString("rating"),
                    rs.getString("song_id"),
                    newline);

            list.add(rating);


        }


        return list;
    }

    public String getSongId() {
        return songId;
    }

    public void setSongId(String songId) {
        this.songId = songId;
    }
}
